package prbase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DB {

    private Connection conn = null;
    private String url = null;
    private Statement st = null;
    private ResultSet rs = null;
    private String sql = null;
    private String username = null;
    private String userpass = null;
    private Boolean lock = false;

    public DB() {
        this.username = Vars.prop.getProperty("Username");
        this.userpass = Vars.prop.getProperty("Userpass");
        this.url = Vars.prop.getProperty("DB_URL");

        if (this.username == null || this.userpass == null || this.url == null) {
            System.out.println("DB.DB(): username,userpass or url == NULL!");
        }
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUserpass() {
        return userpass;
    }

    public void setUserpass(String userpass) {
        this.userpass = userpass;
    }

    private int createConnection() {
        try {
            Properties info = new Properties();
            info.setProperty("user", this.username);
            info.setProperty("password", this.userpass);
            info.setProperty("useUnicode", "true");
            info.setProperty("characterEncoding", "UTF-8");
            this.conn = DriverManager.getConnection(this.url, info);
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } catch (NullPointerException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
        return 0;
    }

    private void closeConnection() {
        try {
            this.st.close();
            this.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NullPointerException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        this.rs = null;
        this.st = null;
        this.conn = null;
        this.sql = null;
    }

    private int createStatement() {
        try {
            this.st = this.conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } catch (NullPointerException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
        return 0;
    }

    private int createResultSet() {
        try {
            this.rs = this.st.executeQuery(this.sql);
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
        return 0;
    }

    public ResultSet getResultSet(String sql) {
        if (!this.lock) {
            this.sql = sql;
            if (this.createConnection() == -1) {
                return null;
            }
            if (this.createStatement() == -1) {
                return null;
            }
            if (this.createResultSet() != 0) {
                this.closeConnection();
                this.lock = false;
                return null;
            }
            this.lock = true;
            return this.rs;
        } else {
            System.out.println("DB.getResultSet(): connection locked");
            return null;
        }
    }

    public void closeResultSet() {
        if (this.lock) {
            this.closeConnection();
            this.lock = false;
        } else {
            System.out.println("DB.closeResultSet(): nothing to close");
        }
    }

    public int executeNonQuery(String sql) {
        if (!this.lock) {
            this.createConnection();
            this.createStatement();
            try {
                this.st.execute(sql);
            } catch (SQLException ex) {
                Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
                this.closeConnection();
                return -1;
            } catch (NullPointerException ex) {
                Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
                this.closeConnection();
                return -1;
            }
            this.closeConnection();
            return 0;
        } else {
            System.out.println("DB.executeNonQuery(): connection locked.");
            return -2;
        }
    }

    public int executeNonQueryAI(String sql) {
        int AI = -1;
        if (!this.lock) {
            this.createConnection();
            this.createStatement();
            try {
                this.st.execute(sql, Statement.RETURN_GENERATED_KEYS);
                this.rs = this.st.getGeneratedKeys();
                this.rs.next();
                AI = this.rs.getInt(1);
            } catch (SQLException ex) {
                System.out.println("DB.executeNonQuery(): " + ex.toString());
            }
            this.closeConnection();
        } else {
            System.out.println("DB.executeNonQuery(): connection locked.");
        }
        return AI;
    }

    public Object getScalar(String sql) {
        Object o = null;
        if (!this.lock) {
            this.sql = sql;
            this.createConnection();
            this.createStatement();
            if (this.createResultSet() != 0) {
                this.closeConnection();
                return null;
            }
            try {
                this.rs.next();
                o = rs.getObject(1);
            } catch (SQLException ex) {
                return null;
            } catch (Exception ex) {
                Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
            this.closeConnection();
        } else {
            System.out.println("DB.executeScalar(): connection locked.");
        }
        return o;
    }

    public String getPrepS(String string) {
        String s;
        s = string.replaceAll("\"", "\"\"");
        return s;
    }

    public static String getDTString(Calendar ca) {
        Integer year = ca.get(Calendar.YEAR);
        String s_year = year.toString();
        Integer month = ca.get(Calendar.MONTH);
        month++;
        String s_month = month.toString();
        if (s_month.length() < 2) {
            s_month = "0" + s_month;
        }
        Integer day = ca.get(Calendar.DAY_OF_MONTH);
        String s_day = day.toString();
        if (s_day.length() < 2) {
            s_day = "0" + s_day;
        }
        Integer hour = ca.get(Calendar.HOUR_OF_DAY);
        String s_hour = hour.toString();
        if (s_hour.length() < 2) {
            s_hour = "0" + s_hour;
        }
        Integer minute = ca.get(Calendar.MINUTE);
        String s_minute = minute.toString();
        if (s_minute.length() < 2) {
            s_minute = "0" + s_minute;
        }
        Integer second = ca.get(Calendar.SECOND);
        String s_second = second.toString();
        if (s_second.length() < 2) {
            s_second = "0" + s_second;
        }
        return s_year + "-" + s_month + "-" + s_day + " " + s_hour + ":" + s_minute +":" + s_second + ".0";
    }
}
